﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Security.Cryptography.X509Certificates;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ExecuteReader
{
    public partial class Form1 : Form
    {
        const string ConStr = "Data Source=.;Initial Catalog=ADO_NET_Simple;Integrated Security=True";
        public Form1()
        {
            InitializeComponent();
        }

        /// <summary>
        /// 使用ExecuteReader读取一行数据
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnOnce_Click(object sender, EventArgs e)
        {
            SqlDataReader dr = ExecuteReader("select top 1 * from Students");
            Student student = ConvertSqlDataReaderToStudent(dr);
            //为了让数据能够在DataGridView里显示，
            List<Student> list = new List<Student>()
            {
                student
            };
            dgvStudentList.DataSource = list;
        }

        /// <summary>
        /// 使用ExecuteReader读取多行行数据
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnMore_Click(object sender, EventArgs e)
        {
            SqlDataReader dr = ExecuteReader("select * from Students");
            List<Student> list = ConvertSqlDataReaderToStudents(dr);
            dgvStudentList.DataSource = list;
        }

        /// <summary>
        /// 获取 SqlDataReader
        /// </summary>
        /// <param name="cmdText"></param>
        /// <returns></returns>
        private SqlDataReader ExecuteReader(string cmdText)
        {
            //连接通道
            SqlConnection conn = new SqlConnection(ConStr);
            //打开链接
            if (conn.State != ConnectionState.Open)
                conn.Open();
            //命令对象
            SqlCommand comm = new SqlCommand();
            //指定链接通道
            comm.Connection = conn;
            //即将执行的sql命令
            comm.CommandText = cmdText;
            //提交命令到数据库
            //ExecuteReader(CommandBehavior.CloseConnection)，
            //在执行该命令时，如果关闭关联的 DataReader 对象，则关联的 Connection 对象也将关闭
            SqlDataReader dr = comm.ExecuteReader(CommandBehavior.CloseConnection);
            return dr;
        }

        /// <summary>
        /// 将 SqlDataReader 对象转换为 Student对象
        /// </summary>
        /// <param name="dr"></param>
        /// <returns></returns>
        private Student ConvertSqlDataReaderToStudent(SqlDataReader dr)
        {
            if (!dr.Read()) return null;
            Student student = new Student
            {
                StudentId = (int)dr["StudentId"],
                StudentName = dr["StudentName"].ToString(),
                StudentGender = (bool)dr["StudentGender"],
                StudentAge = (int)dr["StudentAge"],
                GradeId = (int)dr["GradeId"]
            };
            return student;
        }

        /// <summary>
        /// 将 SqlDataReader 对象转换为 Student列表
        /// </summary>
        /// <param name="dr"></param>
        /// <returns></returns>
        private List<Student> ConvertSqlDataReaderToStudents(SqlDataReader dr)
        {
            List<Student> list = new List<Student>();
            while (dr.Read())
            {
                Student student = new Student
                {
                    StudentId = (int)dr["StudentId"],
                    StudentName = dr["StudentName"].ToString(),
                    StudentGender = (bool)dr["StudentGender"],
                    StudentAge = (int)dr["StudentAge"],
                    GradeId = (int)dr["GradeId"]
                };
                list.Add(student);
            }
            return list;
        }
    }

    /// <summary>
    /// 学生实体类
    /// </summary>
    public class Student
    {
        #region Model

        /// <summary>
        /// 主键
        /// </summary>
        public int StudentId { get; set; }

        /// <summary>
        /// 姓名
        /// </summary>
        public string StudentName { get; set; }

        /// <summary>
        /// 性别 true：男； false：女
        /// </summary>
        public bool StudentGender { get; set; }

        /// <summary>
        /// 年龄
        /// </summary>
        public int StudentAge { get; set; }

        /// <summary>
        /// 所属班级主键
        /// </summary>
        public int GradeId { get; set; }

        #endregion Model
    }
}
